﻿using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace Kivensoft.XlsWriter {
	public class Worksheet {
		public const float DEFAULT_COLUMN_WIDTH = 10f;
		public const float DEFAULT_ROW_HEIGHT = 15f;

		public enum CellDirection { ColumnToColum, RowToRow }

		public enum SheetProtectionValue {
			// sheet, // Is alway on 1 if protected
			objects,
			scenarios,
			formatCells,
			formatColumns,
			formatRows,
			insertColumns,
			insertRows,
			insertHyperlinks,
			deleteColumns,
			deleteRows,
			selectLockedCells,
			sort,
			autoFilter,
			pivotTables,
			selectUnlockedCells
		}

		private Style activeStyle;
		private Workbook workbookReference;
		private string sheetName;
		private int currentRowNumber;
		private int currentColumnNumber;

		public Nullable<Cell.Range> SelectedCells { get; private set; }
		public Dictionary<int, Column> Columns { get; private set; }
		public Nullable<Cell.Range> AutoFilterRange { get; private set; }
		public CellDirection CurrentCellDirection { get; set; }
		public List<SheetProtectionValue> SheetProtectionValues { get; private set; }
		public bool UseSheetProtection { get; set; }
		public string SheetProtectionPassword { get; private set; }

		public string SheetName {
			get { return sheetName; }
			set { SetSheetname(value); }
		}

		public int SheetID { get; set; }

		public Dictionary<string, Cell> Cells { get; private set; }
		public Dictionary<string, Cell.Range> MergedCells { get; private set; }
		public float DefaultRowHeight { get; set; }
		public float DefaultColumnWidth { get; set; }
		public Dictionary<int, float> RowHeights { get; private set; }
		public Dictionary<int, bool> HiddenRows { get; private set; }


		public Worksheet() {
			this.CurrentCellDirection = CellDirection.ColumnToColum;
			Cells = new Dictionary<string, Cell>();
			this.currentRowNumber = 0;
			this.currentColumnNumber = 0;
			DefaultColumnWidth = DEFAULT_COLUMN_WIDTH;
			DefaultRowHeight = DEFAULT_ROW_HEIGHT;
			RowHeights = new Dictionary<int, float>();
			MergedCells = new Dictionary<string, Cell.Range>();
			SheetProtectionValues = new List<SheetProtectionValue>();
			HiddenRows = new Dictionary<int, bool>();
			Columns = new Dictionary<int, Column>();
			this.activeStyle = null;
			this.workbookReference = null;
		}

		public Worksheet(string name, int id) : this() {
			SetSheetname(name);
			this.SheetID = id;
		}

		#region AddNextCell


		public void AddNextCellFormula(string formula) {
			Cell c = new Cell(formula, Cell.CellType.FORMULA, this.currentColumnNumber, this.currentRowNumber);
			AddNextCell(c, true);
		}

		public void AddNextCell(object value) {
			Cell c = new Cell(value, Cell.CellType.DEFAULT, this.currentColumnNumber, this.currentRowNumber);
			AddNextCell(c, true);
		}

		private void AddNextCell(Cell cell, bool incremental) {
			if (this.activeStyle != null) {
				cell.SetStyle(this.activeStyle, this.workbookReference);
			}
			string address = cell.GetCellAddress();
			if (Cells.ContainsKey(address)) {
				Cells[address] = cell;
			}
			else {
				Cells.Add(address, cell);
			}
			if (incremental == true) {
				if (this.CurrentCellDirection == CellDirection.ColumnToColum) {
					this.currentColumnNumber++;
				}
				else {
					this.currentRowNumber++;
				}
			}
			else {
				if (this.CurrentCellDirection == CellDirection.ColumnToColum) {
					this.currentColumnNumber = cell.ColumnAddress + 1;
					this.currentRowNumber = cell.RowAddress;
				}
				else {
					this.currentColumnNumber = cell.ColumnAddress;
					this.currentRowNumber = cell.RowAddress + 1;
				}
			}
		}

		#endregion

		#region AddCell

		public void AddCell(object value, int columnAddress, int rowAddress) {
			Cell c = new Cell(value, Cell.CellType.DEFAULT, columnAddress, rowAddress);
			AddNextCell(c, false);
		}

		public void AddCell(object value, string address) {
			int column, row;
			Cell.ResolveCellCoordinate(address, out column, out row);
			AddCell(value, column, row);
		}

		public void AddCell(Cell cell) {
			AddNextCell(cell, false);
		}

		#endregion

		#region AddCellFormula
		public void AddCellFormula(string formula, string address) {
			int column, row;
			Cell.ResolveCellCoordinate(address, out column, out row);
			Cell c = new Cell(formula, Cell.CellType.FORMULA, column, row);
			AddNextCell(c, false);
		}

		public void AddCellFormula(string formula, int columnAddress, int rowAddress) {
			Cell c = new Cell(formula, Cell.CellType.FORMULA, columnAddress, rowAddress);
			AddNextCell(c, false);
		}
		#endregion

		#region AddCellRange

		public void AddCellRange(List<object> values, Cell.Address startAddress, Cell.Address endAddress) {
			AddCellRangeInternal(values, startAddress, endAddress);
		}

		public void AddCellRange(List<object> values, string cellRange) {
			Cell.Range range = Cell.ResolveCellRange(cellRange);
			AddCellRangeInternal(values, range.StartAddress, range.EndAddress);
		}

		private void AddCellRangeInternal<T>(List<T> values, Cell.Address startAddress, Cell.Address endAddress) {
			List<Cell.Address> addresses = Cell.GetCellRange(startAddress, endAddress);
			if (values.Count != addresses.Count) {
				throw new OutOfRangeException("The number of passed values (" + values.Count.ToString() + ") differs from the number of cells within the range (" + addresses.Count.ToString() + ")");
			}
			List<Cell> list = Cell.ConvertArray<T>(values);
			int len = values.Count;
			for (int i = 0; i < len; i++) {
				list[i].RowAddress = addresses[i].RowIndex;
				list[i].ColumnAddress = addresses[i].ColumnIndex;
				AddNextCell(list[i], false);
			}
		}
		#endregion

		#region RemoveCell
		public bool RemoveCell(int columnAddress, int rowAddress) {
			string address = Cell.ResolveCellAddress(columnAddress, rowAddress);
			if (Cells.ContainsKey(address)) {
				Cells.Remove(address);
				return true;
			}
			else {
				return false;
			}
		}

		public bool RemoveCell(string address) {
			int row, column;
			Cell.ResolveCellCoordinate(address, out column, out row);
			return RemoveCell(column, row);
		}
		#endregion

		public void GoToNextColumn() {
			this.currentColumnNumber++;
			this.currentRowNumber = 0;
		}

		public void GoToNextRow() {
			this.currentRowNumber++;
			this.currentColumnNumber = 0;
		}

		public void SetCurrentRowAddress(int rowAddress) {
			if (rowAddress >= 1048576 || rowAddress < 0) {
				throw new OutOfRangeException("The row number (" + rowAddress.ToString() + ") is out of range. Range is from 0 to 1048575 (1048576 rows).");
			}
			this.currentRowNumber = rowAddress;
		}

		public void SetCurrentColumnAddress(int columnAddress) {
			if (columnAddress >= 16383 || columnAddress < 0) {
				throw new OutOfRangeException("The column number (" + columnAddress.ToString() + ") is out of range. Range is from 0 to 16383 (16384 columns).");
			}
			this.currentColumnNumber = columnAddress;
		}

		public void SetCurentCellAddress(string address) {
			int row, column;
			Cell.ResolveCellCoordinate(address, out column, out row);
			SetCurentCellAddress(column, row);
		}

		public void SetCurentCellAddress(int columnAddress, int rowAddress) {
			SetCurrentColumnAddress(columnAddress);
			SetCurrentRowAddress(rowAddress);
		}

		public void SetSheetname(string name) {
			if (string.IsNullOrEmpty(name)) {
				throw new FormatException("The sheet name must be between 1 and 31 characters");
			}
			if (name.Length > 31) {
				throw new FormatException("The sheet name must be between 1 and 31 characters");
			}
			Regex rx = new Regex(@"[\[\]\*\?/\\]");
			Match mx = rx.Match(name);
			if (mx.Captures.Count > 0) {
				throw new FormatException(@"The sheet name must not contain the characters [  ]  * ? / \ ");
			}
			this.sheetName = name;
		}

		public void SetColumnWidth(string columnAddress, float width) {
			int columnNumber = Cell.ResolveColumn(columnAddress);
			SetColumnWidth(columnNumber, width);
		}

		public void SetColumnWidth(int columnNumber, float width) {
			if (columnNumber >= 16384 || columnNumber < 0) {
				throw new OutOfRangeException("The column number (" + columnNumber.ToString() + ") is out of range. Range is from 0 to 16383 (16384 columns).");
			}
			if (width < 0 || width > 255) {
				throw new OutOfRangeException("The column width (" + width.ToString() + ") is out of range. Range is from 0 to 255 (chars).");
			}
			if (Columns.ContainsKey(columnNumber)) {
				Columns[columnNumber].Width = width;
			}
			else {
				Column c = new Column(columnNumber);
				c.Width = width;
				Columns.Add(columnNumber, c);
			}
		}

		public void SetRowHeight(int rowNumber, float height) {
			if (rowNumber >= 1048576 || rowNumber < 0) {
				throw new OutOfRangeException("The row number (" + rowNumber.ToString() + ") is out of range. Range is from 0 to 1048575 (1048576 rows).");
			}
			if (height < 0 || height > 409.5) {
				throw new OutOfRangeException("The row height (" + height.ToString() + ") is out of range. Range is from 0 to 409.5 (equals 546px).");
			}
			if (RowHeights.ContainsKey(rowNumber)) {
				RowHeights[rowNumber] = height;
			}
			else {
				RowHeights.Add(rowNumber, height);
			}
		}

		public void SetActiveStyle(Style style, Workbook workbookReference) {
			this.activeStyle = style;
			this.workbookReference = workbookReference;
		}

		public void ClearActiveStyle() {
			this.activeStyle = null;
			this.workbookReference = null;
		}

		public string MergeCells(Cell.Range cellRange) {
			return MergeCells(cellRange.StartAddress, cellRange.EndAddress);
		}

		public string MergeCells(string cellRange) {
			Cell.Range range = Cell.ResolveCellRange(cellRange);
			return MergeCells(range.StartAddress, range.EndAddress);
		}

		public string MergeCells(Cell.Address startAddress, Cell.Address endAddress) {
			string key = startAddress.ToString() + ":" + endAddress.ToString();
			Cell.Range value = new Cell.Range(startAddress, endAddress);
			if (MergedCells.ContainsKey(key) == false) {
				MergedCells.Add(key, value);
			}
			return key;
		}

		public void RemoveMergedCells(string range) {
			range = range.ToUpper();
			if (MergedCells.ContainsKey(range) == false) {
				throw new UnknownRangeException("The cell range " + range + " was not found in the list of merged cell ranges");
			}
			else {
				var cs = Cells;
				List<Cell.Address> addresses = Cell.GetCellRange(range);
				Cell cell;
				foreach (Cell.Address address in addresses) {
					if (cs.ContainsKey(addresses.ToString())) {
						cell = cs[address.ToString()];
						cell.Fieldtype = Cell.CellType.DEFAULT; // resets the type
						if (cell.Value == null) {
							cell.Value = string.Empty;
						}
					}
				}
				MergedCells.Remove(range);
			}
		}


		public void AddAllowedActionOnSheetProtection(SheetProtectionValue typeOfProtection) {
			if (SheetProtectionValues.Contains(typeOfProtection) == false) {
				if (typeOfProtection == SheetProtectionValue.selectLockedCells && SheetProtectionValues.Contains(SheetProtectionValue.selectUnlockedCells) == false) {
					SheetProtectionValues.Add(SheetProtectionValue.selectUnlockedCells);
				}
				SheetProtectionValues.Add(typeOfProtection);
				this.UseSheetProtection = true;
			}
		}

		public void SetSheetProtectionPassword(string password) {
			if (string.IsNullOrEmpty(password) == true) {
				SheetProtectionPassword = null;
				return;
			}
			else {
				SheetProtectionPassword = password;
				this.UseSheetProtection = true;
			}
		}

		public void AddHiddenRow(int rowNumber) {
			SetRowHiddenState(rowNumber, true);
		}

		public void RemoveHiddenRow(int rowNumber) {
			SetRowHiddenState(rowNumber, false);
		}

		private void SetRowHiddenState(int rowNumber, bool state) {
			if (rowNumber >= 1048576 || rowNumber < 0) {
				throw new OutOfRangeException("The row number (" + rowNumber.ToString() + ") is out of range. Range is from 0 to 1048575 (1048576 rows).");
			}
			if (HiddenRows.ContainsKey(rowNumber)) {
				if (state == true) {
					HiddenRows.Add(rowNumber, state);
				}
				else {
					HiddenRows.Remove(rowNumber);
				}
			}
			else if (state == true) {
				HiddenRows.Add(rowNumber, state);
			}
		}

		public void AddHiddenColumn(int columnNumber) {
			SetColumnHiddenState(columnNumber, true);
		}

		public void AddHiddenColumn(string columnAddress) {
			int columnNumber = Cell.ResolveColumn(columnAddress);
			SetColumnHiddenState(columnNumber, true);
		}

		public void RemoveHiddenColumn(int columnNumber) {
			SetColumnHiddenState(columnNumber, false);
		}

		public void RemoveHiddenColumn(string columnAddress) {
			int columnNumber = Cell.ResolveColumn(columnAddress);
			SetColumnHiddenState(columnNumber, false);
		}

		private void SetColumnHiddenState(int columnNumber, bool state) {
			if (columnNumber >= 16384 || columnNumber < 0) {
				throw new OutOfRangeException("The column number (" + columnNumber.ToString() + ") is out of range. Range is from 0 to 16383 (16384 columns).");
			}
			if (Columns.ContainsKey(columnNumber) && state == true) {
				Columns[columnNumber].IsHidden = state;
			}
			else if (state == true) {
				Column c = new Column(columnNumber);
				c.IsHidden = state;
				Columns.Add(columnNumber, c);
			}
		}

		public void SetAutoFilter(int startColumn, int endColumn) {
			if (startColumn >= 16384 || startColumn < 0) {
				throw new OutOfRangeException("The start column number (" + startColumn.ToString() + ") is out of range. Range is from 0 to 16383 (16384 columns).");
			}
			if (endColumn >= 16384 || endColumn < 0) {
				throw new OutOfRangeException("The end column number (" + endColumn.ToString() + ") is out of range. Range is from 0 to 16383 (16384 columns).");
			}
			string start = Cell.ResolveCellAddress(startColumn, 0);
			string end = Cell.ResolveCellAddress(endColumn, 0);
			if (endColumn < startColumn) {
				SetAutoFilter(end + ":" + start);
			}
			else {
				SetAutoFilter(start + ":" + end);
			}
		}

		public void SetAutoFilter(string range) {
			AutoFilterRange = Cell.ResolveCellRange(range);
			RecalculateAutoFilter();
			RecalculateColumns();
		}

		public void RemoveAutoFilter() {
			AutoFilterRange = null;
		}

		public void RecalculateAutoFilter() {
			if (AutoFilterRange == null) return;
			int start = AutoFilterRange.Value.StartAddress.ColumnIndex;
			int end = AutoFilterRange.Value.EndAddress.ColumnIndex;
			int endRow = 0;
			foreach (KeyValuePair<string, Cell> item in Cells) {
				if (item.Value.ColumnAddress < start || item.Value.ColumnAddress > end)
					continue;
				if (item.Value.RowAddress > endRow)
					endRow = item.Value.RowAddress;
			}
			Column c;
			for (int i = start; i <= end; i++) {
				if (Columns.ContainsKey(i) == false) {
					c = new Column(i);
					c.HasAutoFilter = true;
					Columns.Add(i, c);
				}
				else {
					Columns[i].HasAutoFilter = true;
				}
			}
			Cell.Range temp = new Cell.Range();
			temp.StartAddress = new Cell.Address(start, 0);
			temp.EndAddress = new Cell.Address(end, endRow);
			AutoFilterRange = temp;
		}

		public void RecalculateColumns() {
			List<int> columnsToDelete = new List<int>();
			foreach (KeyValuePair<int, Column> col in Columns) {
				if (col.Value.HasAutoFilter || col.Value.IsHidden || col.Value.Width == Worksheet.DEFAULT_COLUMN_WIDTH) {
					columnsToDelete.Add(col.Key);
				}
			}
			foreach (int index in columnsToDelete) {
				Columns.Remove(index);
			}
		}

		public void SetSelectedCells(Cell.Range range) {
			SelectedCells = range;
		}

		public void SetSelectedCells(Cell.Address startAddress, Cell.Address endAddress) {
			SelectedCells = new Cell.Range(startAddress, endAddress);
		}

		public void SetSelectedCells(string range) {
			SelectedCells = Cell.ResolveCellRange(range);
		}

		public void RemoveSelectedCells() {
			SelectedCells = null;
		}

		public class Column {
			private int number;
			private string columnAddress;

			public int Number {
				get { return number; }
				set {
					this.columnAddress = Cell.ResolveColumnAddress(value);
					number = value;
				}
			}

			public string ColumnAddress {
				get { return columnAddress; }
				set {
					this.number = Cell.ResolveColumn(value);
					this.columnAddress = value;
				}
			}

			public float Width { get; set; }
			public bool IsHidden { get; set; }
			public bool HasAutoFilter { get; set; }

			public Column() {
				this.Width = Worksheet.DEFAULT_COLUMN_WIDTH;
			}

			public Column(int columnCoordinate) : this() {
				this.Number = columnCoordinate;
			}

			public Column(string columnAddress) : this() {
				this.ColumnAddress = columnAddress;
			}

		}
	}
}
